package db

import (
	"context"
	"fmt"
	"github.com/jackc/pgx/v5/pgxpool"
	"time"
)

// 初始化数据库连接池
func initDBPool(connString string) (*pgxpool.Pool, error) {
	dbConfig, err := pgxpool.ParseConfig(connString)
	if err != nil {
		return nil, fmt.Errorf("can not parse db connection string: %v", err)
	}

	// 配置连接池大小
	dbConfig.MaxConns = 30                     // 最大连接数
	dbConfig.MinConns = 5                      // 最小连接数
	dbConfig.MaxConnLifetime = 1 * time.Minute // 单个连接的最大生命周期
	dbConfig.MaxConnIdleTime = 1 * time.Minute // 空闲连接的最大保留时间

	// 创建连接池
	pool, err := pgxpool.NewWithConfig(context.Background(), dbConfig)
	if err != nil {
		return nil, fmt.Errorf("can not create db pool: %v", err)
	}

	// 测试连接池
	if err := pool.Ping(context.Background()); err != nil {
		return nil, fmt.Errorf("can not connect to db: %v", err)
	}

	logger.Infof("init db pool success")

	return pool, nil
}

// 初始化数据库表
func initTable(ctx context.Context, pool *pgxpool.Conn) error {
	if logger == nil {
		return fmt.Errorf("logger not initialized")
	}
	if ctx == nil {
		logger.Errorf("context is nil")
		return fmt.Errorf("context is nil")
	}
	if pool == nil {
		logger.Errorf("db pool is nil")
		return fmt.Errorf("db pool is nil")
	}

	// 表定义和创建语句
	tables := []TableInfo{
		{
			Name: adminTable,
			CreateQuery: fmt.Sprintf(`
				CREATE TABLE %s.%s (
					id VARCHAR(255) PRIMARY KEY,
					username VARCHAR(255) NOT NULL,
					password VARCHAR(255) NOT NULL,
					status INTEGER
				)
			`, schema, adminTable),
		},
		{
			Name: employeeTable,
			CreateQuery: fmt.Sprintf(`
				CREATE TABLE %s.%s (
					id VARCHAR(255) PRIMARY KEY,
					username VARCHAR(255) NOT NULL,
					password VARCHAR(255) NOT NULL,
					status INTEGER
				)
			`, schema, employeeTable),
		},
		{
			Name: courseTable,
			CreateQuery: fmt.Sprintf(`
				CREATE TABLE %s.%s (
					id VARCHAR(255) PRIMARY KEY,
					name VARCHAR(255) NOT NULL,
					admin_id VARCHAR(255) NOT NULL,
				    description VARCHAR(255),
				    number INTEGER,
				    duration VARCHAR(255),
					status INTEGER,
					CONSTRAINT fk_admin FOREIGN KEY (admin_id) REFERENCES %s.%s (id)
				)
			`, schema, courseTable, schema, adminTable),
		},
		{
			Name: enrollmentTable,
			CreateQuery: fmt.Sprintf(`
				CREATE TABLE %s.%s (
					id VARCHAR(255) PRIMARY KEY,
					employee_id VARCHAR(255) NOT NULL,
					course_id VARCHAR(255) NOT NULL,
					status INTEGER,
					CONSTRAINT fk_employee FOREIGN KEY (employee_id) REFERENCES %s.%s (id),
					CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES %s.%s (id)
				)
			`, schema, enrollmentTable, schema, employeeTable, schema, courseTable),
		},
		{
			Name: feedbackTable,
			CreateQuery: fmt.Sprintf(`
				CREATE TABLE %s.%s (
					id VARCHAR(255) PRIMARY KEY,
					employee_id VARCHAR(255) NOT NULL,
					course_id VARCHAR(255) NOT NULL,
					content VARCHAR(255) NOT NULL,
					create_time TIMESTAMP NOT NULL,
					status INTEGER,
					CONSTRAINT fk_feedback_employee FOREIGN KEY (employee_id) REFERENCES %s.%s (id),
					CONSTRAINT fk_feedback_course FOREIGN KEY (course_id) REFERENCES %s.%s (id)
				)
			`, schema, feedbackTable, schema, employeeTable, schema, courseTable),
		},
		{
			Name: progressTable,
			CreateQuery: fmt.Sprintf(`
				CREATE TABLE %s.%s (
					progress_id VARCHAR(255) PRIMARY KEY NOT NULL,
					employee_id VARCHAR(255) NOT NULL,
					status INTEGER NOT NULL,
				    CONSTRAINT fk_progress_employee FOREIGN KEY (employee_id) REFERENCES %s.%s (id)
				)
			`, schema, progressTable, schema, employeeTable),
		},
	}

	// 检查和创建表
	for _, table := range tables {
		if err := ensureTable(ctx, pool, table); err != nil {
			return err
		}
	}

	logger.Info("All tables initialized successfully")
	return nil
}

func initSchema(ctx context.Context, pool *pgxpool.Conn) error {
	// 检查 schema 是否存在
	query := `
		SELECT EXISTS (
			SELECT 1
			FROM information_schema.schemata
			WHERE schema_name = $1
		)
	`
	var exists bool
	err := pool.QueryRow(ctx, query, schema).Scan(&exists)
	if err != nil {
		logger.Errorf("Error checking schema existence: %v", err)
		return fmt.Errorf("failed to check schema existence: %w", err)
	}

	// 如果 schema 不存在，则创建
	if !exists {
		createSchemaQuery := fmt.Sprintf(`CREATE SCHEMA %s`, schema)
		_, err := pool.Exec(ctx, createSchemaQuery)
		if err != nil {
			logger.Errorf("Error creating schema %s: %v", schema, err)
			return fmt.Errorf("failed to create schema %s: %w", schema, err)
		}
		logger.Infof("Schema %s created successfully", schema)
	} else {
		logger.Infof("Schema %s already exists", schema)
	}

	return nil
}

// 初始化测试用户
func initTestUser(ctx context.Context, pool *pgxpool.Conn) error {
	testAdminId := time.Now().Format("20060102150405")
	testAdminName := "admin"
	testAdminPwd := "pwd"
	testAdminStatus := 0

	testEmployeeId := time.Now().Format("20060102150405")
	testEmployeeName := "employee"
	testEmployeePwd := "pwd"
	testEmployeeStatus := 0

	// 检查表中是否已存在测试管理员
	testAdminQuery := fmt.Sprintf(`
		SELECT EXISTS (
			SELECT 1
			FROM %s.%s
			WHERE username = $1
		)
	`, schema, adminTable)
	var testAdminExists bool
	err := pool.QueryRow(ctx, testAdminQuery, testAdminName).Scan(&testAdminExists)
	if err != nil {
		logger.Errorf("Error checking test admin existence: %v", err)
		return fmt.Errorf("failed to check test admin existence: %w", err)
	}

	// 检查表中是否已存在测试员工
	testEmployeeQuery := fmt.Sprintf(`
		SELECT EXISTS (
			SELECT 1
			FROM %s.%s
			WHERE username = $1
		)
	`, schema, employeeTable)
	var testEmployeeExists bool
	err = pool.QueryRow(ctx, testEmployeeQuery, testEmployeeName).Scan(&testEmployeeExists)
	if err != nil {
		logger.Errorf("Error checking test employee existence: %v", err)
		return fmt.Errorf("failed to check test employee existence: %w", err)
	}

	if !testAdminExists {
		// 将管理员数据插入 t_admin 表
		testAdminQuery = fmt.Sprintf(`
		INSERT INTO %s.%s (id, username, password, status)
		VALUES ($1, $2, $3, $4)
	`, schema, adminTable)
		_, err = pool.Exec(ctx, testAdminQuery, testAdminId, testAdminName, testAdminPwd, testAdminStatus)
		if err != nil {
			logger.Errorf("Error inserting test admin data: %v", err)
			return fmt.Errorf("failed to insert test admin data: %w", err)
		}
	}

	if !testEmployeeExists {
		// 将员工数据插入 t_employee 表
		testEmployeeQuery = fmt.Sprintf(`
		INSERT INTO %s.%s (id, username, password, status)
		VALUES ($1, $2, $3, $4)
	`, schema, employeeTable)
		_, err = pool.Exec(ctx, testEmployeeQuery, testEmployeeId, testEmployeeName, testEmployeePwd, testEmployeeStatus)
		if err != nil {
			logger.Errorf("Error inserting test employee data: %v", err)
			return fmt.Errorf("failed to insert test employee data: %w", err)
		}
	}

	logger.Info("Test user data inserted successfully")

	return nil
}

// 检查表是否存在，如果不存在则创建
func ensureTable(ctx context.Context, pool *pgxpool.Conn, table TableInfo) error {
	query := fmt.Sprintf(`
		SELECT EXISTS (
			SELECT 1
			FROM information_schema.tables
			WHERE table_schema = '%s'
			AND table_name = '%s'
		)
	`, schema, table.Name)

	var exists bool
	err := pool.QueryRow(ctx, query).Scan(&exists)
	if err != nil {
		logger.Errorf("Failed to check if table %s exists: %v", table.Name, err)
		return fmt.Errorf("failed to check if table %s exists: %w", table.Name, err)
	}

	if !exists {
		_, err := pool.Exec(ctx, table.CreateQuery)
		if err != nil {
			logger.Errorf("Failed to create table %s: %v", table.Name, err)
			return fmt.Errorf("failed to create table %s: %w", table.Name, err)
		}
		logger.Infof("Table %s created successfully", table.Name)
	} else {
		logger.Infof("Table %s already exists", table.Name)
	}

	return nil
}
